USE AdventureWorks2012;
GO

-- 5-1. Summarizing a Result Set
SELECT  SUM(i.Quantity) AS Total
FROM    Production.ProductInventory i;
GO

-- 5-2. Creating Summary Groups
SELECT  OrderDate,
        SUM(TotalDue) AS TotalDueByOrderDate
FROM    Sales.SalesOrderHeader
WHERE   OrderDate >= '2005-07-01T00:00:00'
        AND OrderDate < '2005-08-01T00:00:00'
GROUP BY OrderDate;
GO

-- 5-3. Restricting a Result Set to Groups of Interest
SELECT  s.Name,
        COUNT(w.WorkOrderID) AS Cnt
FROM    Production.ScrapReason s
        INNER JOIN Production.WorkOrder w
            ON s.ScrapReasonID = w.ScrapReasonID
GROUP BY s.Name
HAVING  COUNT(*) > 50;
GO

-- 5-4. Removing Duplicates from the Detailed Results
SELECT  [RateChangeDate],
        COUNT([Rate]) AS [Count],
        COUNT(DISTINCT Rate) AS [DistinctCount]
FROM    [HumanResources].[EmployeePayHistory]
WHERE   RateChangeDate >= '20030101'
        AND RateChangeDate < '20030110'
GROUP BY RateChangeDate;
GO

-- 5-5. Creating Summary Cubes
SELECT  i.Shelf,
        i.LocationID,
        SUM(i.Quantity) AS Total
FROM    Production.ProductInventory i
GROUP BY CUBE(i.Shelf, i.LocationID);
GO

-- 5-6. Creating Hierarchical Summaries
SELECT  i.Shelf,
        p.Name,
        SUM(i.Quantity) AS Total
FROM    Production.ProductInventory i
        INNER JOIN Production.Product p
            ON i.ProductID = p.ProductID
GROUP BY ROLLUP(i.Shelf, p.Name);
GO

-- 5-7. Creating Custom Summaries
SELECT  NULL AS Shelf,
        i.LocationID,
        p.Name,
        SUM(i.Quantity) AS Total
FROM    Production.ProductInventory i
        INNER JOIN Production.Product p
            ON i.ProductID = p.ProductID
WHERE   Shelf IN ('A', 'C')
        AND Name IN ('Chain', 'Decal', 'Head Tube')
GROUP BY i.LocationID,
        p.Name
UNION ALL
SELECT  i.Shelf,
        NULL,
        NULL,
        SUM(i.Quantity) AS Total
FROM    Production.ProductInventory i
        INNER JOIN Production.Product p
            ON i.ProductID = p.ProductID
WHERE   Shelf IN ('A', 'C')
        AND Name IN ('Chain', 'Decal', 'Head Tube')
GROUP BY i.Shelf
UNION ALL
SELECT  i.Shelf,
        NULL,
        p.Name,
        SUM(i.Quantity) AS Total
FROM    Production.ProductInventory i
        INNER JOIN Production.Product p
            ON i.ProductID = p.ProductID
WHERE   Shelf IN ('A', 'C')
        AND Name IN ('Chain', 'Decal', 'Head Tube')
GROUP BY i.Shelf,
        p.Name;


SELECT  i.Shelf,
        i.LocationID,
        p.Name,
        SUM(i.Quantity) AS Total
FROM    Production.ProductInventory i
        INNER JOIN Production.Product p
            ON i.ProductID = p.ProductID
WHERE   Shelf IN ('A', 'C')
        AND Name IN ('Chain', 'Decal', 'Head Tube')
GROUP BY GROUPING SETS((i.Shelf), 
                       (i.Shelf, p.Name), 
                       (i.LocationID, p.Name));
GO

-- 5-8. Identifying Rows Generated by the GROUP BY Arguments
SELECT  i.Shelf,
        i.LocationID,
        CASE WHEN GROUPING(i.Shelf) = 0
                  AND GROUPING(i.LocationID) = 1 THEN 'Shelf Total'
             WHEN GROUPING(i.Shelf) = 1
                  AND GROUPING(i.LocationID) = 0 THEN 'Location Total'
             WHEN GROUPING(i.Shelf) = 1
                  AND GROUPING(i.LocationID) = 1 THEN 'Grand Total'
             ELSE 'Regular Row'
        END AS RowType,
        SUM(i.Quantity) AS Total
FROM    Production.ProductInventory i
WHERE   LocationID = 2
GROUP BY CUBE(i.Shelf, i.LocationID);
GO

-- 5-9. Identifying Summary Levels
SELECT  i.Shelf,
        i.LocationID,
        i.Bin,
        CASE GROUPING_ID(i.Shelf, i.LocationID, i.Bin)
          WHEN 1 THEN 'Shelf/Location Total'
          WHEN 2 THEN 'Shelf/Bin Total'
          WHEN 3 THEN 'Shelf Total'
          WHEN 4 THEN 'Location/Bin Total'
          WHEN 5 THEN 'Location Total'
          WHEN 6 THEN 'Bin Total'
          WHEN 7 THEN 'Grand Total'
          ELSE 'Regular Row'
        END AS GroupingType,
        SUM(i.Quantity) AS Total
FROM    Production.ProductInventory i
WHERE   i.LocationID IN (3)
        AND i.Bin IN (1, 2)
GROUP BY CUBE(i.Shelf, i.LocationID, i.Bin)
ORDER BY i.Shelf,
        i.LocationID,
        i.Bin;


SELECT  i.Shelf,
        i.LocationID,
        i.Bin,
        i.Quantity
FROM    Production.ProductInventory i
WHERE   i.LocationID IN (3)
        AND i.Bin IN (1, 2);

SELECT  i.Shelf,
        i.LocationID,
        i.Bin,
        SUM(i.Quantity) AS Total
FROM    Production.ProductInventory i
WHERE   i.LocationID IN (3)
        AND i.Bin IN (1, 2)
GROUP BY CUBE(i.Shelf, i.LocationID, i.Bin)
ORDER BY i.Shelf,
        i.LocationID,
        i.Bin;
GO

